For the current database, gives you space oriented statistics for each partition of indexes (even if you only have one partion), like row count, page counts, etc. Resembles the sysindexes in previous versions of SQL Server, with more information.

Type: view

Data: accumulating, refreshed at server restart

Scope: Reset on server restart (or object/partition drop and recreate)

Columns:

• partition_id – numeric identifier of the partition (relates to sys.partitons)
• object_id – the object_id of the object that the partition belongs to
• index_id – identifies the index on the table. 0 = Heap
• partition_number – 1 for non-partitioned tables. For partitioned tables will be the 1 – based number of the partition.
• in_row_data_page_count – the number of pages being used for data of the object. Can be the leaf pages of an index or the data pages of a clustered table or heap
• in_row_used_page_count – Includes all pages in use for the object, including non-leaf index and index allocation map pages.
• in_row_reserved_page_count – Includes all pages in use plus any pages reserved for the object, even if the pages are not currently in use.
• lob_used_page_count – Count of pages in use for the object to store out-of-row values such as varchar(max), varbinary(max), etc.
• lob_reserved_page_count – Count of out of the row pages including any that are reserved but not in use.
• row_overflow_used_page_count – Count of pages that are in use for storing overflow data for rows that are larger than will fit on a single ~8K page
• row_overflow_reserved_page_count – Count of overlow pages that includes any pages that are reserved but not in use
• used_page_count – Total number of pages in use in the partiton for any reason
• reserved_page_count – Total number of pages in use or reserved in the partiton for any reason
• row_count – The number of rows in the table

Examples:

Get rowcount of tables. Note that I grouped on the object_id, because for a partitioned table, you need to add all of the rows in all partitions.

select object_name(dm_db_partition_stats.object_id),
           sum(dm_db_partition_stats.row_count) as row_count
from sys.dm_db_partition_stats
              join sys.indexes
                     on indexes.object_id = dm_db_partition_stats.object_id
                          and indexes.index_id = dm_db_partition_stats.index_id
where indexes.type_desc in (‘CLUSTERED’,’HEAP’)
group by dm_db_partition_stats.object_id

Other planned examples include a sample partition to show the rowcounts/usage, and summations of some of the other values to give a meaningful data other than rowcount.